package util.file;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.text.ParseException;
import java.util.Collection;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.Map;
import java.util.Set;

import model.BasePo;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import util.DateUtils;
import util.LoggerUtils;

public class ExcelWriter {
  private String title;
  private String[] headers;
  private String[] keys;
  private Collection<?> data;
  private String version;
  // 必填列，控制样式标红
  private Set<String> requiredKeys = new HashSet<>();
  // 为日期类型字段指定格式的Map
  private Map<String, String> formateDate;

  private Map<String, Method> getterCache;
  private Class<?>[] paramTypes = new Class<?>[] {};
  private Object[] callingArgs = new Object[] {};

  /**
   * 创建Excel输出类(xlsx)
   * 
   * @param title sheet的标题
   * @param headers 标题行
   * @param keys 列对应的field
   * @param data 数据
   */
  public ExcelWriter(String title, String[] headers, String[] keys, Collection<?> data) {
    this(title, headers, keys, data, ExcelUtils.EXCEL_VERSION_XLSX);
  }

  /**
   * 创建Excel输出类(指定是xls还是xlsx)
   * 
   * @param title sheet的标题
   * @param headers 标题行
   * @param keys 列对应的field
   * @param data 数据
   * @param version 版本
   */
  public ExcelWriter(String title, String[] headers, String[] keys, Collection<?> data, String version) {
    this.title = title;
    this.headers = headers;
    this.keys = keys;
    this.data = data;
    this.version = version;
    getterCache = new HashMap<>();
  }

  /**
   * 直接返回输入流，结合Struts的StreamResult较方便
   * 
   * @return
   */
  public InputStream newInputStream() {
    ByteArrayOutputStream bos = new ByteArrayOutputStream();
    writeTo(bos);
    return new ByteArrayInputStream(bos.toByteArray());
  }

  /**
   * 输出到指定的输出流
   * 
   * @param os
   */
  public void writeTo(OutputStream os) {
    try {
      generateWorkbook().write(os);
    } catch (IOException e) {
      LoggerUtils.errorInfo("导出Excel时出现IO异常:", e);
      throw new RuntimeException(e);
    }
  }

  /**
   * 返回Workbook对象，便于调用者进行个性化定制
   * 
   * @return
   */
  public Workbook generateWorkbook() {
    Workbook workbook = newWorkbook();
    Sheet sheet = workbook.createSheet(title);
    sheet.setDefaultColumnWidth(10);

    Font headerFont = workbook.createFont();
    headerFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    headerFont.setFontName("Arial");
    headerFont.setFontHeightInPoints((short) 10);

    Font requiredHeaderFont = workbook.createFont();
    requiredHeaderFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
    requiredHeaderFont.setFontName("Arial");
    requiredHeaderFont.setColor(Font.COLOR_RED);
    requiredHeaderFont.setFontHeightInPoints((short) 10);

    Font plainTextFont = workbook.createFont();
    plainTextFont.setFontName("Arial");
    plainTextFont.setFontHeightInPoints((short) 10);

    CellStyle headerStyle = workbook.createCellStyle();
    headerStyle.setFont(headerFont);

    CellStyle requiredHeaderStyle = workbook.createCellStyle();
    requiredHeaderStyle.setFont(requiredHeaderFont);

    CellStyle numStyle = workbook.createCellStyle();
    numStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0.00"));// (自定义货币)格式
    numStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 右对齐
    numStyle.setFont(plainTextFont);

    CellStyle numNotDotStyle = workbook.createCellStyle();
    numNotDotStyle.setDataFormat(HSSFDataFormat.getBuiltinFormat("#,##0"));// (数字类型
    numNotDotStyle.setAlignment(CellStyle.ALIGN_RIGHT);// 右对齐
    numNotDotStyle.setFont(plainTextFont);

    CellStyle textStyle = workbook.createCellStyle();
    textStyle.setFont(plainTextFont);

    // 标题行
    Row row = sheet.createRow(0);
    for (int i = 0; i < headers.length; i++) {
      Cell cell = row.createCell(i);
      if (requiredKeys.contains(keys[i]))
        cell.setCellStyle(requiredHeaderStyle);
      else
        cell.setCellStyle(headerStyle);
      cell.setCellValue(headers[i]);
    }

    Iterator<?> it = data.iterator();
    int index = 1;
    while (it.hasNext()) {
      row = sheet.createRow(index);
      try {
        fillRow(row, it.next(), index++, textStyle, numStyle, numNotDotStyle);
      } catch (ReflectiveOperationException e) {
        LoggerUtils.errorInfo("导出Excel时出现反射调用异常:", e);
      }
    }
    return workbook;
  }

  private Row fillRow(Row row, Object o, int rowIndex, CellStyle cellStyle, CellStyle numCellStyle,
      CellStyle numNotDotStyle) throws ReflectiveOperationException {
    int index = 0;
    for (String key : keys) {
      Cell cell = row.createCell(index++);
      Object fieldValue = "";

      if (o instanceof BasePo) {
        Method method = null;
        if (rowIndex == 1) {
          method = o.getClass().getMethod("get" + Character.toUpperCase(key.charAt(0)) + key.substring(1), paramTypes);
          getterCache.put(key, method);
        } else {
          method = getterCache.get(key);
        }
        fieldValue = method.invoke(o, callingArgs);
      } else if (o instanceof Map) {
        fieldValue = ((Map<?, ?>) o).get(key);
      }

      cell.setCellStyle(cellStyle);
      if (fieldValue == null) {
        cell.setCellValue("");
      } else {
        if (fieldValue instanceof Number) {// 数字
          if (fieldValue instanceof BigDecimal || fieldValue instanceof Double || fieldValue instanceof Float) {
            cell.setCellStyle(numCellStyle);
          } else if (fieldValue instanceof BigInteger || fieldValue instanceof Integer) {
            cell.setCellStyle(numNotDotStyle);
          }
          cell.setCellValue(Double.valueOf(fieldValue.toString()));
        } else if (fieldValue instanceof Date) {// 日期
          if (formateDate != null && formateDate.get(key) != null) {
            cell.setCellValue(DateUtils.formatDate((Date) fieldValue, formateDate.get(key)));
          } else {
            cell.setCellValue(DateUtils.formatDate((Date) fieldValue, DateUtils.DEFAULT_DATE_FORMAT));
          }
        } else {// 其他为字符串
          if (StringUtils.isNotEmpty(fieldValue.toString()) && formateDate != null && formateDate.containsKey(key)) {
            try {
              Date date = org.apache.commons.lang.time.DateUtils.parseDate(fieldValue.toString(),
                  ExcelUtils.PARSEPATTERNS);
              cell.setCellValue(DateUtils.formatDate(date, formateDate.get(key)));
            } catch (ParseException e) {
              LoggerUtils.errorInfo("无法将数据转换为日期格式：" + fieldValue.toString());
              cell.setCellValue(fieldValue.toString());
            }
          } else {
            cell.setCellValue(fieldValue.toString());
          }
        }
      }
    }
    return row;
  }

  private Workbook newWorkbook() {
    switch (version) {
      case ExcelUtils.EXCEL_VERSION_XLS:
        return new HSSFWorkbook();
      default:
        return new SXSSFWorkbook();// modify by fqh SXSSFWorkbook大数据量excel写入操作，
        // return new XSSFWorkbook();
    }
  }

  public void setRequiredKeys(Set<String> requiredKeys) {
    this.requiredKeys = requiredKeys;
  }

  public void setRequiredKeys(String... keys) {
    Collections.addAll(requiredKeys, keys);
  }

  public void setFormateDate(Map<String, String> formateDate) {
    this.formateDate = formateDate;
  }

}
